﻿using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;

namespace OwnZTec.DbCommon
{
    public delegate void Mapeamento<in T>(IDataReader data, T obj) where T : class, new();

    public class DbCommon
    {
        #region -- Membros e Construtores -------------------------------------------------------------------------------------------------
        private const string ConnStr =
            "Data Source=dbOwnZTec.mssql.somee.com;Initial Catalog=dbOwnZTec;Persist Security Info=True;User ID=ownztec;Password=djdjango;Packet Size=4096;Workstation ID=dbOwnZTec.mssql.somee.com";

        private readonly SqlConnection _sqlCon;
        private readonly SqlCommand _sqlCmd;

        public List<SqlParameter> Parametros;
        public CommandType TipoComando;
        public string Comando;

        public DbCommon()
        {
            _sqlCon = new SqlConnection(ConnStr);
            _sqlCmd = new SqlCommand { Connection = _sqlCon };

            Parametros = new List<SqlParameter>();
        }
        #endregion ------------------------------------------------------------------------------------------------------------------------

        #region -- Metodos ----------------------------------------------------------------------------------------------------------------
        // -- Manipulacao de parametros  --------------------------------------------------------------------------------------------------
        public SqlParameter CriarParametro(string name, object valor, ParameterDirection direcao)
        {
            return new SqlParameter { ParameterName = name.StartsWith("@") ? name : "@" + name, Value = valor, Direction = direcao };
        }

        public SqlParameter CriarParametro(string name, object valor, int tamanho, ParameterDirection direcao)
        {
            var sqlParm = CriarParametro(name.StartsWith("@") ? name : "@" + name, valor, direcao);
            sqlParm.Size = tamanho;

            return sqlParm;
        }

        // -- Metodos de execucao ----------------------------------------------------------------------------------------------------------
        private void PrepararComando()
        {
            Parametros.ForEach(p => _sqlCmd.Parameters.Add(p));
            _sqlCmd.CommandType = TipoComando;
            _sqlCmd.CommandText = Comando;
        }

        public void ExecutarSemRetorno()
        {
            PrepararComando();

            _sqlCon.Open();

            _sqlCmd.ExecuteNonQuery();

            _sqlCon.Close();
        }

        public List<T> ExecutarLista<T>(Mapeamento<T> funcaoMapeamento) where T : class, new()
        {
            PrepararComando();

            var list = new List<T>();

            _sqlCon.Open();

            using (var data = _sqlCmd.ExecuteReader())
            {
                while (data.Read())
                {
                    var item = new T();
                    funcaoMapeamento.Invoke(data, item);
                    list.Add(item);
                }
            }

            _sqlCon.Close();

            return list;
        }

        public T ExecutarObjeto<T>(Mapeamento<T> funcaoMapeamento) where T : class, new()
        {
            PrepararComando();

            _sqlCon.Open();

            var item = new T();

            using (var data = _sqlCmd.ExecuteReader())
            {
                while (data.Read())
                {
                    funcaoMapeamento.Invoke(data, item);

                    _sqlCon.Close();

                    return item;
                }
            }

            _sqlCon.Close();

            return null;
        }
        #endregion ------------------------------------------------------------------------------------------------------------------------
    }
}
